﻿using Aspose.Cells;
using Aspose.Cells.Drawing;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace Fuli.Tool
{
    /// <summary>
    /// DataTble转换Excel帮助类，使用Aspose.Cells8.3.0.0
    /// </summary>
    public static class ExcelHelperAspose
    {
        /// <summary>
        /// DataTble转换为Excel，并返回数据流
        #region 控制器中下载的写法

        /// <summary>
        /// List导出Excel
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="title">标题</param>
        /// <param name="description">说明</param>
        /// <param name="list">实体列表对象</param>
        public static void ExportToExcel<T>(string title, string description, List<T> list)
        {
            HttpContext curContext = HttpContext.Current;
            HttpResponse response = curContext.Response;

            byte[] bt = null;
            if (list != null)
            {
                string error = "";
                DataTable dt = new DataTable();
                dt = ExcelHelperAspose.ToDataTableDescription<T>(list);
                bt = ExcelHelperAspose.DataTableToExcelByte(dt, title, description, out error);
            }
            else
            {
                response.End();
            }
            string fileName = title + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";//客户端保存的文件名
            //以字符流的形式下载文件 
            response.ContentType = "application/vnd.ms-excel";
            //通知浏览器下载文件而不是打开
            response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            if (bt != null)
            {
                response.BinaryWrite(bt);
            }
            response.Flush();
            response.End();
        }

        #endregion
        /// </summary>
        /// <param name="datatable">数据表</param>
        /// <param name="title">标题</param>
        /// <param name="description">说明：String.Format("时间段：{0}至{1}", DateTime.Now.AddDays(-1).ToString("yyyy年MM月dd日"), DateTime.Now.ToString("yyyy年MM月dd日"));</param>
        /// <param name="error">错误信息返回</param>
        /// <returns></returns>
        public static byte[] DataTableToExcelByte(DataTable dt, string title, string description, out string error)
        {
            Workbook workbook = DataTableToExcel(dt, title, description, out error);
            byte[] bt = null;
            if (string.IsNullOrEmpty(error) && workbook != null)
            {
                try
                {
                    System.IO.MemoryStream ms = workbook.SaveToStream();//生成数据流 
                    bt = ms.ToArray();
                }
                catch (Exception ex)
                {
                    error = error + " DataTableToExcel: " + ex.Message;
                }
            }
            return bt;
        }

        /// <summary>
        /// DataTble转换为Excel，并保存到指定路径
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="filepath">保存文件路径</param>
        /// <param name="title">标题</param>
        /// <param name="description">说明：String.Format("时间段：{0}至{1}", DateTime.Now.AddDays(-1).ToString("yyyy年MM月dd日"), DateTime.Now.ToString("yyyy年MM月dd日"));</param>
        /// <param name="error">错误信息返回</param>
        /// <returns></returns>
        public static bool DataTableToExcelSave(DataTable dt, string filepath, string title, string description, out string error)
        {
            Workbook workbook = DataTableToExcel(dt, title, description, out error);
            bool b = false;
            if (string.IsNullOrEmpty(error) && workbook != null)
            {
                try
                {
                    workbook.Save(filepath);//保存到硬盘 
                    b = true;
                }
                catch (Exception ex)
                {
                    error = error + " DataTableToExcel: " + ex.Message;
                }
            }
            return b;
        }

        /// <summary>
        /// DataTble转换为Excel
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="title">标题</param>
        /// <param name="description">说明</param>
        /// <param name="error">错误信息返回</param>
        /// <returns></returns>
        public static Workbook DataTableToExcel(DataTable dt, string title, string description, out string error)
        {
            error = "";
            try
            {
                if (dt == null || dt.Columns.Count < 1)
                {
                    error = "DataTableToExcel:datatable 为空";
                    return null;
                }
                int cellsCount = dt.Columns.Count;
                string tableTitle = title;//datatable.TableName;

                //新建工作簿 
                Workbook workbook = new Workbook(); //工作簿 
                Worksheet sheet = workbook.Worksheets[0]; //工作表 
                Cells cells = sheet.Cells;//单元格
                Style style = workbook.Styles[workbook.Styles.Add()];//为单元格添加样式

                ////设置居中
                //style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                ////设置背景颜色
                //style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0);
                //style.Pattern = BackgroundType.Solid;
                //style.Font.IsBold = true;

                #region 表头
                //标题 
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中  
                style.Font.Name = "宋体";//文字字体 
                style.Font.Size = 18;//文字大小  
                style.Font.IsBold = true;//粗体
                cells.Merge(0, 0, 1, cellsCount);               //合并单元格 
                cells[0, 0].PutValue(tableTitle);   //填写内容 
                cells[0, 0].SetStyle(style);            //给单元格关联样式  
                cells.SetRowHeight(0, 28);              //设置行高 

                //描述
                style.HorizontalAlignment = TextAlignmentType.Left;
                style.Font.Size = 11;
                style.Font.IsBold = false;
                cells.Merge(1, 0, 1, cellsCount);
                cells[1, 0].PutValue(description + "  " + String.Format("统计时间:{0}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
                cells[1, 0].SetStyle(style);
                cells.SetRowHeight(1, 20);
                ////统计时间
                //style.HorizontalAlignment = TextAlignmentType.Right;
                //style.Font.Size = 11;
                //style.Font.IsBold = false;
                //cells.Merge(1, 7, 1, 5);
                //cells[1, 7].PutValue("");
                //cells[1, 7].SetStyle(style);
                //cells.SetRowHeight(1, 20);
                #endregion

                #region 表格标题行
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    style.HorizontalAlignment = TextAlignmentType.Center;
                    style.Font.Size = 12;
                    style.Font.IsBold = true;
                    DataColumn col = dt.Columns[i];
                    string columnName = col.Caption ?? col.ColumnName;
                    cells[2, i].PutValue(columnName);
                    cells[2, i].SetStyle(style);
                    cells.SetRowHeight(2, 20);
                    // cells.SetColumnWidthPixel(0, 38);
                }
                #endregion

                #region 表格内容
                int nRow = 3;//开始行
                foreach (DataRow row in dt.Rows)
                {
                    try
                    {
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
                            {
                                //------插入图片数据-------
                                System.Drawing.Image image = (System.Drawing.Image)row[i];
                                MemoryStream mstream = new MemoryStream();
                                image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
                                sheet.Pictures.Add(nRow, i, mstream);
                            }
                            else if (row[i].GetType().ToString() == "System.DateTime")
                            {
                                cells[nRow, i].PutValue(Convert.ToDateTime(row[i]).ToString("yyyy-MM-dd HH:mm:ss"));
                            }
                            else
                            {
                                cells[nRow, i].PutValue(row[i]);
                            }
                        }
                    }
                    catch (System.Exception e)
                    {
                        error = error + " DataTableToExcel: " + e.Message;
                    }
                    nRow++;
                }

                #endregion

                //？？
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    sheet.AutoFitColumn(k, 0, 150);
                }
                sheet.FreezePanes(1, 0, 1, dt.Columns.Count);

                return workbook;
            }
            catch (Exception ex)
            {
                error = error + " DataTableToExcel: " + ex.Message;
                return null;
            }
        }

        /// <summary>
        /// 简单：DataTble转换为Excel，并返回数据流
        /// </summary>
        /// <param name="datatable">数据表</param>
        /// <param name="title">标题</param>
        /// <param name="description">说明：String.Format("时间段：{0}至{1}", DateTime.Now.AddDays(-1).ToString("yyyy年MM月dd日"), DateTime.Now.ToString("yyyy年MM月dd日"));</param>
        /// <param name="error">错误信息返回</param>
        /// <returns></returns>
        public static byte[] DataTableToExcelByte2(DataTable dt, out string error)
        {
            Workbook workbook = DataTableToExcel2(dt, out error);
            byte[] bt = null;
            if (string.IsNullOrEmpty(error) && workbook != null)
            {
                try
                {
                    System.IO.MemoryStream ms = workbook.SaveToStream();//生成数据流 
                    bt = ms.ToArray();
                }
                catch (Exception ex)
                {
                    error = error + " DataTableToExcel: " + ex.Message;
                }
            }
            return bt;
        }
        /// <summary>
        /// 简单：DataTble转换为Excel
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="error">错误信息返回</param>
        /// <returns></returns>
        public static Workbook DataTableToExcel2(DataTable dt, out string error)
        {
            error = "";
            try
            {
                if (dt == null || dt.Columns.Count < 1)
                {
                    error = "DataTableToExcel:datatable 为空";
                    return null;
                }
                int cellsCount = dt.Columns.Count;

                //新建工作簿 
                Workbook workbook = new Workbook(); //工作簿 
                Worksheet sheet = workbook.Worksheets[0]; //工作表 
                Cells cells = sheet.Cells;//单元格
                Style style = workbook.Styles[workbook.Styles.Add()];//为单元格添加样式

                #region 表格标题行
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    style.HorizontalAlignment = TextAlignmentType.Center;
                    style.Font.Size = 12;
                    style.Font.IsBold = true;
                    DataColumn col = dt.Columns[i];
                    string columnName = col.Caption ?? col.ColumnName;
                    cells[0, i].PutValue(columnName);
                    cells[0, i].SetStyle(style);
                    cells.SetRowHeight(0, 20);
                    // cells.SetColumnWidthPixel(0, 38);
                }
                #endregion

                #region 表格内容(添加模板示例)
                int nRow = 1;//开始行
                if(dt.Rows.Count>0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        try
                        {
                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                if (row[i].GetType().ToString() == "System.Drawing.Bitmap")
                                {
                                    //------插入图片数据-------
                                    System.Drawing.Image image = (System.Drawing.Image)row[i];
                                    MemoryStream mstream = new MemoryStream();
                                    image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
                                    sheet.Pictures.Add(nRow, i, mstream);
                                }
                                else if (row[i].GetType().ToString() == "System.DateTime")
                                {
                                    cells[nRow, i].PutValue(Convert.ToDateTime(row[i]).ToString("yyyy-MM-dd HH:mm:ss"));
                                }
                                else
                                {
                                    cells[nRow, i].PutValue(row[i]);
                                }
                            }
                        }
                        catch (System.Exception e)
                        {
                            error = error + " DataTableToExcel: " + e.Message;
                        }
                        nRow++;
                    }
                }

                #endregion

                //？？
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    sheet.AutoFitColumn(k, 0, 150);
                }
                sheet.FreezePanes(1, 0, 1, dt.Columns.Count);

                return workbook;
            }
            catch (Exception ex)
            {
                error = error + " DataTableToExcel: " + ex.Message;
                return null;
            }
        }

        /// <summary>
        /// Excel文件转换为DataTable.
        /// </summary>
        /// <param name="filepath">Excel文件的全路径</param>
        /// <param name="datatable">DataTable:返回值</param>
        /// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
        /// <returns>true:函数正确执行 false:函数执行错误</returns>
        public static bool ExcelToDataTable(string fileName, out DataTable datatable, out string error)
        {
            error = "";
            datatable = null;
            try
            {
                if (File.Exists(fileName) == false)
                {
                    error = "文件不存在";
                    datatable = null;
                    return false;
                }
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fileName);
                //workbook.Open(fileName);
                Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
                datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
                //-------------图片处理-------------PictureCollection
                PictureCollection pictures = worksheet.Pictures;
                if (pictures.Count > 0)
                {
                    //string error2 = "";
                    //if (InsertPicturesIntoDataTable(pictures, datatable, out datatable, out error2) == false)
                    //{
                    //    error = error + error2;
                    //}
                }
                return true;
            }
            catch (System.Exception e)
            {
                error = e.Message;
                return false;
            }
        }

        #region 类型转换

        /// <summary>  
        /// DataTable转换为List 
        /// </summary>  
        /// <typeparam name="TResult">类型</typeparam>  
        /// <param name="dt">DataTable</param>  
        /// <returns></returns>  
        public static List<T> ToList<T>(this DataTable dt) where T : class, new()
        {
            //创建一个属性的列表  
            List<PropertyInfo> prlist = new List<PropertyInfo>();
            //获取TResult的类型实例  反射的入口  

            Type t = typeof(T);

            //获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表   
            Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });

            //创建返回的集合  

            List<T> oblist = new List<T>();

            foreach (DataRow row in dt.Rows)
            {
                //创建TResult的实例  
                T ob = new T();
                //找到对应的数据  并赋值  
                prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
                //放入到返回的集合中.  
                oblist.Add(ob);
            }
            return oblist;
        }



        /// <summary>
        /// List转化一个DataTable(属性名为excel字段名)  
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable ToDataTableName<T>(IList<T> list)
        {
            //创建属性的集合  
            List<PropertyInfo> pList = new List<PropertyInfo>();
            //获得反射的入口  
            Type type = typeof(T);
            DataTable dt = new DataTable();
            //把所有的public属性加入到集合 并添加DataTable的列  
            Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); });
            foreach (var item in list)
            {
                //创建一个DataRow实例  
                DataRow row = dt.NewRow();
                //给row 赋值  
                pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
                //加入到DataTable  
                dt.Rows.Add(row);
            }
            return dt;
        }

        /// <summary>  
        /// List转化一个DataTable (属性的描述为excel字段名,只有描述的属性才加入DataTable中)  
        /// </summary>  
        /// <typeparam name="T">集合项类型</typeparam>  
        /// <param name="list">集合</param>  
        /// <returns>数据集(表)</returns>  
        public static DataTable ToDataTableDescription<T>(IList<T> list)
        {
            //创建属性的集合  
            List<PropertyInfo> pList = new List<PropertyInfo>();
            //获得反射的入口  

            Type type = typeof(T);
            DataTable dt = new DataTable();
            //把所有的public属性加入到集合 并添加DataTable的列  

            Array.ForEach<PropertyInfo>(type.GetProperties(), p =>
            {
                string name = "";
                var temp = (DescriptionAttribute[])p.GetCustomAttributes(typeof(System.ComponentModel.DescriptionAttribute), false);
                if (temp != null && temp.Length > 0)
                {
                    name = temp[0].Description;
                    pList.Add(p);
                    dt.Columns.Add(name, p.PropertyType);
                }
            });
            foreach (var item in list)
            {
                //创建一个DataRow实例  
                DataRow row = dt.NewRow();
                //给row 赋值  
                foreach (PropertyInfo p in pList)
                {
                    string name = "";
                    var temp = (DescriptionAttribute[])p.GetCustomAttributes(typeof(System.ComponentModel.DescriptionAttribute), false);
                    if (temp != null && temp.Length > 0)
                    {
                        name = temp[0].Description;
                        row[name] = p.GetValue(item, null);
                    }
                }
                //加入到DataTable  
                dt.Rows.Add(row);
            }
            return dt;
        }

        #endregion

    }
}
